package com.google.code.rgbgoesbetween.data.sqlite;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.google.code.rgbgoesbetween.data.AccountStatus;
import com.google.code.rgbgoesbetween.data.Datastore;
import com.google.code.rgbgoesbetween.data.Item;
import com.google.code.rgbgoesbetween.data.Item.ItemStatus;
import com.google.code.rgbgoesbetween.data.Profile;
import com.google.code.rgbgoesbetween.data.QueryStatus;
import com.google.code.rgbgoesbetween.data.SearchEngine;
import com.google.code.rgbgoesbetween.data.SearchEngineCriteria;

/**
 * Implemented SQLite database using Datastore and SearchEngine interfaces. Stores and 
 * creates app data such as Items, Profiles, etc.
 * This uses the set of constants found in the SQLiteDatabase class.
 * 
 * @see SQLiteDBManager
 * @author Avanyali
 *
 */
public class SQLiteDatastore implements Datastore, SearchEngine {

	// Database fields
	  private SQLiteDatabase database; 
	  private SQLiteDBManager dbManager;
	  
	  private String[] allProfileColumns = new String[] { 
			  SQLiteDBManager.COLUMN_PROFILE_ID, SQLiteDBManager.COLUMN_PROFILE_USER,
			  SQLiteDBManager.COLUMN_PROFILE_PASS, SQLiteDBManager.COLUMN_PROFILE_STATUS,
			  SQLiteDBManager.COLUMN_PROFILE_ADDRESS, SQLiteDBManager.COLUMN_PROFILE_PHONE, 
			  SQLiteDBManager.COLUMN_PROFILE_EMAIL
	  };
	  
	  private String[] allItemColumns = new String[] {
			  SQLiteDBManager.COLUMN_ITEM_ID, SQLiteDBManager.COLUMN_ITEM_NAME,
			  SQLiteDBManager.COLUMN_ITEM_STATUS, SQLiteDBManager.COLUMN_ITEM_DESCRIPTION, 
			  SQLiteDBManager.COLUMN_ITEM_DATESTART, SQLiteDBManager.COLUMN_ITEM_DATEEND, 
			  SQLiteDBManager.COLUMN_ITEM_LOCATION, SQLiteDBManager.COLUMN_ITEM_TAGS
	  };
	  
	  private String[] allReportColumns = new String[] {
			  SQLiteDBManager.COLUMN_REPORT_ID,
			  SQLiteDBManager.COLUMN_REPORT_REPORTERID,
			  SQLiteDBManager.COLUMN_REPORT_ITEMID
	  };

	  private AccountStatus[] profileEnums = AccountStatus.values();
	  
	  /*
	   * int id, String username, String passHash, AccountStatus accountStatus,
			String address, String phone, String email)
	   */
	  public SQLiteDatastore(Context context) {
	    dbManager = new SQLiteDBManager(context);
	    Profile profile = new Profile(-10, "admin", "D033E22AE348AEB5660FC2140AEC35850C4DA997", 
	    		AccountStatus.ADMIN_MEMBER, "", "", "");
	    createProfile(profile);
	  }

	  /**
	   * Sets the database to one that allows insertion and updates
	   * @throws SQLException
	   */
	  public void openWritableDB() throws SQLException {
	    database = dbManager.getWritableDatabase();
	  }
	  
	  /**
	   * Sets the database to one that allows queries only
	   * @throws SQLException
	   */
	  public void openReadableDB() throws SQLException {
		  database = dbManager.getReadableDatabase();
	  }

	  /**
	   * Closes the database
	   */
	  public void closeDB() {
	    dbManager.close();
	  }
	
	  @Override
	  public Profile getAccountInfo(String username){
		openReadableDB();
		
		// Make a DB query.
		Cursor c = database.query(SQLiteDBManager.TABLE_PROFILES, allProfileColumns, 
				SQLiteDBManager.COLUMN_PROFILE_USER + " = '" + username + "'", 
			    null, null, null, null);
		if (c.getCount() != 1) { // Either 0 or multiple results returned
			c.close();
			closeDB();
			return null;
		}
		
		// Get the profile
		c.moveToFirst();
		Profile p = new Profile(
				c.getInt(0), c.getString(1), c.getString(2), profileEnums[c.getInt(3)],
				c.getString(4), c.getString(5), c.getString(6)
		);
		c.close();
		
		// Get the IDs of each item belonging to this profile
		c = database.query(SQLiteDBManager.TABLE_REPORTS, allReportColumns, 
				SQLiteDBManager.COLUMN_REPORT_REPORTERID + " = " + p.getId(), 
				null, null, null, null);
		List<Integer> ids = new ArrayList<Integer>();
		if (c.getCount() > 0) {
			while (c.moveToNext())
				ids.add(c.getInt(2));
		}
		p.setItemIDList(ids);
		
		c.close();
		closeDB();
		
		return p;
	}

	@Override
	public Profile createProfile(Profile profile) {
		// First check to see if this profile already exists by making a username query.
		openReadableDB();
		Cursor c = database.query(SQLiteDBManager.TABLE_PROFILES, allProfileColumns, 
				SQLiteDBManager.COLUMN_PROFILE_USER + " = '" + profile.getUsername() + "';",
				null, null, null, null);
		Log.d("SQLiteDatastore", "c: " + c.getCount());
		int count = c.getCount();
		c.close();
		if (count > 0)
			return null;
		closeDB();
		
		// Now insert the profile into the database
		openWritableDB();
		ContentValues vals = new ContentValues();
		vals.put(SQLiteDBManager.COLUMN_PROFILE_USER, profile.getUsername());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_PASS, profile.getPassHash());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_STATUS, profile.getAccountStatus() != null ?
				Integer.valueOf(profile.getAccountStatus().ordinal()) : 
					Integer.valueOf(AccountStatus.STANDARD_MEMBER.ordinal()));
		vals.put(SQLiteDBManager.COLUMN_PROFILE_ADDRESS, profile.getAddress());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_PHONE, profile.getPhoneNumber());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_EMAIL, profile.getEmail());
		database.insert(SQLiteDBManager.TABLE_PROFILES, null, vals);
		closeDB();
		
		return getAccountInfo(profile.getUsername());
	}

	@Override
	public QueryStatus updateProfile(Profile profile) {
		openWritableDB();
		ContentValues vals = new ContentValues();
		vals.put(SQLiteDBManager.COLUMN_PROFILE_USER, profile.getUsername());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_PASS, profile.getPassHash());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_STATUS, profile.getAccountStatus().ordinal());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_ADDRESS, profile.getAddress());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_PHONE, profile.getPhoneNumber());
		vals.put(SQLiteDBManager.COLUMN_PROFILE_EMAIL, profile.getEmail());
		int rowsAffected = database.update(SQLiteDBManager.TABLE_PROFILES, vals, 
				SQLiteDBManager.COLUMN_PROFILE_ID + "=" + profile.getId(), null); 
		
		closeDB();
		Log.d("SQLiteDatastore", "ID affected: " + profile.getId());
		return rowsAffected == 1 ? QueryStatus.QUERY_SUCCESS: QueryStatus.QUERY_FAIL;
	}
	
	@Override
	public QueryStatus deleteProfile(Profile profile)
	{
		openWritableDB();
		//int rowsAffected = database.delete(SQLiteDBManager.TABLE_PROFILES, 
		//		SQLiteDBManager.COLUMN_PROFILE_ID + "=" + profile.getId(), null);
		ContentValues vals = new ContentValues();
		vals.put(SQLiteDBManager.COLUMN_PROFILE_STATUS, AccountStatus.MEMBER_DELETED.ordinal());
		int rowsAffected = database.update(SQLiteDBManager.TABLE_PROFILES, vals, 
				SQLiteDBManager.COLUMN_PROFILE_ID + "=" + profile.getId(), null);
		closeDB();
		return rowsAffected == 0 ? QueryStatus.QUERY_FAIL : QueryStatus.QUERY_SUCCESS;
	}

	@Override
	public Item createItem(Item item) {
		openWritableDB();
		
		StringBuilder sb = new StringBuilder();
		for (String s : item.getTags()) {
			sb.append(s + ",");
		}
		if (sb.toString().endsWith(","))
			sb.deleteCharAt(sb.length()-1);
		
		ContentValues vals = new ContentValues();
		vals.put(SQLiteDBManager.COLUMN_ITEM_NAME, item.getName());
		vals.put(SQLiteDBManager.COLUMN_ITEM_DESCRIPTION, item.getDescription());
		vals.put(SQLiteDBManager.COLUMN_ITEM_STATUS, item.getStatus().ordinal());
		vals.put(SQLiteDBManager.COLUMN_ITEM_DATESTART, Calendar.getInstance().getTimeInMillis());
		vals.put(SQLiteDBManager.COLUMN_ITEM_LOCATION, item.getLocation());
		vals.put(SQLiteDBManager.COLUMN_ITEM_TAGS, sb.toString());
		long id = database.insert(SQLiteDBManager.TABLE_ITEMS, null, vals);
		
		vals.clear();
		vals.put(SQLiteDBManager.COLUMN_REPORT_ITEMID, id);
		vals.put(SQLiteDBManager.COLUMN_REPORT_REPORTERID, item.getReporter().getId());
		database.insert(SQLiteDBManager.TABLE_REPORTS, null, vals);
		closeDB();
		
		if (id == -1)
			return null;
		
		item.setId((int) id);
		return item;
	}
		

	@Override
	public QueryStatus updateItem(Item item) {
		// TODO Auto-generated method stub
		return QueryStatus.METHOD_NOT_IMPLEMENTED;
	}

	@Override
	public QueryStatus deleteItem(Item item) {
		// TODO Auto-generated method stub
		return QueryStatus.METHOD_NOT_IMPLEMENTED;
	}

	
	/*
	 * SQLiteDBManager.COLUMN_ITEM_ID, SQLiteDBManager.COLUMN_ITEM_NAME,
			  SQLiteDBManager.COLUMN_ITEM_STATUS, SQLiteDBManager.COLUMN_ITEM_DESCRIPTION, 
			  SQLiteDBManager.COLUMN_ITEM_DATESTART, SQLiteDBManager.COLUMN_ITEM_DATEEND, 
			  SQLiteDBManager.COLUMN_ITEM_LOCATION, SQLiteDBManager.COLUMN_ITEM_TAGS
	 */
	public Item requestItem(int id) {
		openReadableDB();
		// Make a DB query.
		Cursor c = database.query(SQLiteDBManager.TABLE_ITEMS, allItemColumns, 
				SQLiteDBManager.COLUMN_ITEM_ID + " = '" + id + "'", 
			    null, null, null, null);
		if (c.getCount() != 1) { // Either 0 or multiple results returned
			c.close();
			closeDB();
			return null;
		}
		
		// Get the profile
		c.moveToFirst();
		Item i = new Item();
		i.setId(id);
		i.setName(c.getString(1));
		i.setDescription(c.getString(3));
		i.setStatus(ItemStatus.values()[c.getInt(2)]);
		Calendar time = Calendar.getInstance();
		time.setTimeInMillis(c.getLong(4));
		i.setDateRange(new Calendar[] {time});
		i.setLocation(c.getString(6));
		i.setTags(c.getString(7).split(","));
				
		c.close();
		closeDB();
		
		return i;
	}

	@Override
	public int[] getMatchingIDs(SearchEngineCriteria se) {
		openReadableDB();
		String sb = "";
		sb = SQLiteDBManager.COLUMN_ITEM_STATUS + "=" + (se.getStatus()? 0 : 1);
		if (se.getCategory() != null) 
			sb = join(sb, SQLiteDBManager.COLUMN_ITEM_CATEGORY + " LIKE '" + se.getCategory() + "' ");
		if (se.getDescription() != null) 
			sb = join(sb, SQLiteDBManager.COLUMN_ITEM_DESCRIPTION + " LIKE '" + se.getDescription() + "' ");
		if (se.getDateStart() != 0) 
			sb = join(sb, SQLiteDBManager.COLUMN_ITEM_DATESTART + ">=" + se.getDateStart());		
		
		Log.e("Searching for", sb.toString());
		Cursor c = database.query(SQLiteDBManager.TABLE_ITEMS, new String[] {SQLiteDBManager.COLUMN_ITEM_ID}, sb.toString(), null, 
				null, null, null, null);
		if (c.getCount() == 0)
			return new int[0];
		int[] ids = new int[c.getCount()];
		c.moveToFirst();
		for (int i = 0; i < ids.length; i++) {
			ids[i] = c.getInt(0);
			c.moveToNext();
		}
		return ids;
	}
	
	/**
	 * Helps to construct a search query for the Search Engine
	 * and will concatenate any type of search method used, and this
	 * accounts for null strings as well. 
	 * @param str1
	 * @param str2
	 * @return A joined string with AND if neither are null
	 */
	private String join(String str1, String str2) {
		if (str1 == null || str1.equals(""))
			return str2;
		else if (str2 == null || str2.equals("")) 
			return str1;
		else
			return str1 + " AND " + str2;
	}

}
